package com.zucai365.football.dao.impl;

import java.sql.SQLException;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.stereotype.Repository;

import com.wcj.common.dao.impl.WcjDaoImpl;

import com.zucai365.football.bean.CurrentFootballTeamBean;
import com.zucai365.football.dao.CurrentFootballTeamDao;

@Repository("currentFootballTeamDao")
public class CurrentFootballTeamDaoImpl extends WcjDaoImpl<CurrentFootballTeamBean> implements CurrentFootballTeamDao{

	@Override
	public void saveFootballTeamJiFen(Long lianSaiId, Long lianSaiYearId) {
		
		final StringBuffer sql = new StringBuffer();
		
		sql.append( " UPDATE t_current_football_team AA" );
		sql.append( " ,(" );
		sql.append( " SELECT A.FID,A.LIANSAI_ID,A.LIANSAI_YEAR_ID,A.TEAMNAME , SUM(A.JIFEN) AS JIFEN" );
		sql.append( " FROM (" );
		sql.append( " (SELECT T.ZD_ID AS FID,T.LIANSAI_ID ,T.LIANSAI_YEAR_ID , t.ZD_NAME as TEAMNAME ,SUM(t.ZD_JIFEN) AS JIFEN" );
		sql.append( " from t_team_dz t" );
		sql.append( " where t.RESULT is not NULL AND  T.LIANSAI_YEAR_ID="+lianSaiYearId+" AND T.LIANSAI_ID="+lianSaiId );
		sql.append( " GROUP BY FID,TEAMNAME,LIANSAI_ID,LIANSAI_YEAR_ID" );
		sql.append( " ORDER BY JIFEN DESC)" );
		sql.append( " UNION" );
		sql.append( " (SELECT T.KD_ID AS FID,T.LIANSAI_ID ,T.LIANSAI_YEAR_ID , t.KD_NAME as TEAMNAME ,SUM(t.KD_JIFEN) AS JIFEN" );
		sql.append( " from t_team_dz t" );
		sql.append( " where t.RESULT is not NULL AND  T.LIANSAI_YEAR_ID="+lianSaiYearId+" AND T.LIANSAI_ID="+lianSaiId );
		sql.append( " GROUP BY FID,TEAMNAME,LIANSAI_ID,LIANSAI_YEAR_ID" );
		sql.append( " ORDER BY JIFEN DESC)" );
		sql.append( " ) A" );
		sql.append( " GROUP BY A.FID,A.LIANSAI_ID,A.LIANSAI_YEAR_ID,A.TEAMNAME" );
		sql.append( " ORDER BY JIFEN DESC" );
		sql.append( " ) BB " );
		sql.append( " SET AA.SUM_JIFEN=BB.JIFEN" );
		sql.append( " WHERE AA.FOOTBALL_TEAM_ID=BB.FID AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		
		final StringBuffer zcsql = new StringBuffer();
		zcsql.append( " UPDATE t_current_football_team AA" );
		zcsql.append( " ,(" );
		zcsql.append( " SELECT T.ZD_ID AS FID,T.LIANSAI_ID ,T.LIANSAI_YEAR_ID , t.ZD_NAME as TEAMNAME ,SUM(t.ZD_JIFEN) AS JIFEN" );
		zcsql.append( " from t_team_dz t" );
		zcsql.append( " where t.RESULT is not NULL AND  T.LIANSAI_YEAR_ID="+lianSaiYearId+" AND T.LIANSAI_ID="+lianSaiId );
		zcsql.append( " GROUP BY FID,TEAMNAME,LIANSAI_ID,LIANSAI_YEAR_ID" );
		zcsql.append( " ORDER BY JIFEN DESC" );
		zcsql.append( " ) BB " );
		zcsql.append( " SET AA.ZC_JIFEN=BB.JIFEN" );
		zcsql.append( " WHERE AA.FOOTBALL_TEAM_ID=BB.FID AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		
		final StringBuffer kcsql = new StringBuffer();
		kcsql.append( " UPDATE t_current_football_team AA" );
		kcsql.append( " ,(" );
		kcsql.append( " SELECT T.KD_ID AS FID,T.LIANSAI_ID ,T.LIANSAI_YEAR_ID , t.KD_NAME as TEAMNAME ,SUM(t.KD_JIFEN) AS JIFEN" );
		kcsql.append( " from t_team_dz t" );
		kcsql.append( " where t.RESULT is not NULL AND  T.LIANSAI_YEAR_ID="+lianSaiYearId+" AND T.LIANSAI_ID="+lianSaiId );
		kcsql.append( " GROUP BY FID,TEAMNAME,LIANSAI_ID,LIANSAI_YEAR_ID" );
		kcsql.append( " ORDER BY JIFEN DESC" );
		kcsql.append( " ) BB " );
		kcsql.append( " SET AA.KC_JIFEN=BB.JIFEN" );
		kcsql.append( " WHERE AA.FOOTBALL_TEAM_ID=BB.FID AND AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID" );
		
		this.getHibernateTemplate().execute( new HibernateCallback() {
			@Override
			public Object doInHibernate( Session session ) throws HibernateException,
					SQLException {
				session.createSQLQuery( sql.toString() ).executeUpdate();
				session.createSQLQuery( zcsql.toString() ).executeUpdate();
				session.createSQLQuery( kcsql.toString() ).executeUpdate();
				return null;
			}
		});
		
	}

	@Override
	public void saveFootballTeamJinQiu(Long lianSaiId, Long lianSaiYearId) {
		
		final StringBuffer sql = new StringBuffer();
		sql.append( " UPDATE t_current_football_team AA" );
		sql.append( " ,(" );
		sql.append( " SELECT B.LIANSAI_ID,B.LIANSAI_YEAR_ID,B.EVENT_FOOTBALL_TEAM_ID,B.EVENT_FOOTBALL_TEAM_NAME,COUNT(B.EVENT) AS JINQIUCOUNT" );
		sql.append( " FROM t_football_player A ,t_game_fire B" );
		sql.append( " WHERE A.ID=B.FOOTBALL_PLAYER_ID" );
		sql.append( " AND B.EVENT IN ('进球','点球','乌龙球')" );
		sql.append( " AND B.LIANSAI_ID="+lianSaiId+" AND B.LIANSAI_YEAR_ID="+lianSaiYearId );
		sql.append( " GROUP BY B.LIANSAI_ID,B.LIANSAI_YEAR_ID,B.EVENT_FOOTBALL_TEAM_ID,B.EVENT_FOOTBALL_TEAM_NAME" );
		sql.append( " ORDER BY JINQIUCOUNT DESC" );
		sql.append( " ) BB" );
		sql.append( " SET AA.SUM_JINQIU=BB.JINQIUCOUNT" );
		sql.append( " WHERE AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		
		final StringBuffer zcsql = new StringBuffer();
		zcsql.append( " UPDATE t_current_football_team AA" );
		zcsql.append( " ,(" );
		zcsql.append( " SELECT B.LIANSAI_ID,B.LIANSAI_YEAR_ID,B.EVENT_FOOTBALL_TEAM_ID,B.EVENT_FOOTBALL_TEAM_NAME,COUNT(B.EVENT) AS JINQIUCOUNT" );
		zcsql.append( " FROM t_football_player A ,t_game_fire B" );
		zcsql.append( " WHERE A.ID=B.FOOTBALL_PLAYER_ID" );
		zcsql.append( " AND B.EVENT IN ('进球','点球','乌龙球')" );
		zcsql.append( " AND B.EVENT_STATUS='主场'" );
		zcsql.append( " AND B.LIANSAI_ID="+lianSaiId+" AND B.LIANSAI_YEAR_ID="+lianSaiYearId );
		zcsql.append( "" );
		zcsql.append( " GROUP BY B.LIANSAI_ID,B.LIANSAI_YEAR_ID,B.EVENT_FOOTBALL_TEAM_ID,B.EVENT_FOOTBALL_TEAM_NAME" );
		zcsql.append( " ORDER BY JINQIUCOUNT DESC" );
		zcsql.append( " ) BB" );
		zcsql.append( " SET AA.ZC_JINQIU=BB.JINQIUCOUNT" );
		zcsql.append( " WHERE AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		
		
		final StringBuffer kcsql = new StringBuffer();
		kcsql.append( " UPDATE t_current_football_team AA" );
		kcsql.append( " ,(" );
		kcsql.append( " SELECT B.LIANSAI_ID,B.LIANSAI_YEAR_ID,B.EVENT_FOOTBALL_TEAM_ID,B.EVENT_FOOTBALL_TEAM_NAME,COUNT(B.EVENT) AS JINQIUCOUNT" );
		kcsql.append( " FROM t_football_player A ,t_game_fire B" );
		kcsql.append( " WHERE A.ID=B.FOOTBALL_PLAYER_ID" );
		kcsql.append( " AND B.EVENT IN ('进球','点球','乌龙球')" );
		kcsql.append( " AND B.EVENT_STATUS='客场'" );
		kcsql.append( " AND B.LIANSAI_ID="+lianSaiId+" AND B.LIANSAI_YEAR_ID="+lianSaiYearId );
		kcsql.append( "" );
		kcsql.append( " GROUP BY B.LIANSAI_ID,B.LIANSAI_YEAR_ID,B.EVENT_FOOTBALL_TEAM_ID,B.EVENT_FOOTBALL_TEAM_NAME" );
		kcsql.append( " ORDER BY JINQIUCOUNT DESC" );
		kcsql.append( " ) BB" );
		kcsql.append( " SET AA.KC_JINQIU=BB.JINQIUCOUNT" );
		kcsql.append( " WHERE AA.LIANSAI_ID=BB.LIANSAI_ID AND AA.LIANSAI_YEAR_ID=BB.LIANSAI_YEAR_ID AND AA.FOOTBALL_TEAM_ID=BB.EVENT_FOOTBALL_TEAM_ID" );
		
		
			
		
		
		this.getHibernateTemplate().execute( new HibernateCallback() {
			@Override
			public Object doInHibernate( Session session ) throws HibernateException,
					SQLException {
				session.createSQLQuery( sql.toString() ).executeUpdate();
				session.createSQLQuery( zcsql.toString() ).executeUpdate();
				session.createSQLQuery( kcsql.toString() ).executeUpdate();
				
				return null;
			}
		});
		
	}
	
}
